package com.wutongyu.mannyburger;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

public class OrderDatabaseHelper extends SQLiteOpenHelper {
    private static final String TAG = "OrderDatabaseHelper";
    private static final String DATABASE_NAME = "orders.db";
    private static final int DATABASE_VERSION = 4;
    private static final String TABLE_ORDERS = "orders";
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_ITEMS = "items";
    private static final String COLUMN_NOTE = "note";
    private static final String COLUMN_TOTAL_PRICE = "total_price";
    private static final String COLUMN_USER_PHONE = "user_phone";
    private static final String COLUMN_TIMESTAMP = "timestamp";
    private static final String COLUMN_DELIVERY_TYPE = "delivery_type";
    private static final String COLUMN_DELIVERY_ADDRESS = "delivery_address";
    private static final String COLUMN_DELIVERY_PHONE = "delivery_phone";

    public OrderDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTableQuery = "CREATE TABLE " + TABLE_ORDERS + " (" +
                COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                COLUMN_ITEMS + " TEXT, " +
                COLUMN_NOTE + " TEXT, " +
                COLUMN_TOTAL_PRICE + " REAL, " +
                COLUMN_USER_PHONE + " TEXT, " +
                COLUMN_TIMESTAMP + " TEXT, " +
                COLUMN_DELIVERY_TYPE + " TEXT, " +
                COLUMN_DELIVERY_ADDRESS + " TEXT, " +
                COLUMN_DELIVERY_PHONE + " TEXT)";
        db.execSQL(createTableQuery);
        Log.d(TAG, "数据库表创建成功: " + TABLE_ORDERS);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "升级数据库从版本 " + oldVersion + " 到 " + newVersion);
        
        try {
            // 逐步升级数据库版本
            if (oldVersion < 2) {
                // 从版本1升级到版本2：添加用户手机号列
                Log.d(TAG, "添加列: " + COLUMN_USER_PHONE);
                db.execSQL("ALTER TABLE " + TABLE_ORDERS + " ADD COLUMN " + COLUMN_USER_PHONE + " TEXT");
            }
            
            if (oldVersion < 3) {
                // 从版本2升级到版本3：添加时间戳列
                Log.d(TAG, "添加列: " + COLUMN_TIMESTAMP);
                try {
                    db.execSQL("ALTER TABLE " + TABLE_ORDERS + " ADD COLUMN " + COLUMN_TIMESTAMP + " TEXT");
                    
                    // 为现有记录添加默认时间戳
                    String currentTime = getCurrentTimestamp();
                    ContentValues values = new ContentValues();
                    values.put(COLUMN_TIMESTAMP, currentTime);
                    db.update(TABLE_ORDERS, values, COLUMN_TIMESTAMP + " IS NULL", null);
                    Log.d(TAG, "更新现有记录时间戳为: " + currentTime);
                } catch (Exception e) {
                    Log.e(TAG, "添加时间戳列时出错: " + e.getMessage());
                }
            }
            
            if (oldVersion < 4) {
                // 从版本3升级到版本4：添加送餐方式、地址和电话列
                Log.d(TAG, "添加送餐相关列");
                try {
                    db.execSQL("ALTER TABLE " + TABLE_ORDERS + " ADD COLUMN " + COLUMN_DELIVERY_TYPE + " TEXT DEFAULT '自提'");
                    db.execSQL("ALTER TABLE " + TABLE_ORDERS + " ADD COLUMN " + COLUMN_DELIVERY_ADDRESS + " TEXT");
                    db.execSQL("ALTER TABLE " + TABLE_ORDERS + " ADD COLUMN " + COLUMN_DELIVERY_PHONE + " TEXT");
                    Log.d(TAG, "送餐相关列添加成功");
                } catch (Exception e) {
                    Log.e(TAG, "添加送餐相关列时出错: " + e.getMessage());
                }
            }
            
            // 未来版本的升级可以在这里添加
            
        } catch (Exception e) {
            // 如果升级出现严重错误，可以选择重建表
            Log.e(TAG, "数据库升级失败，重建表: " + e.getMessage());
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_ORDERS);
            onCreate(db);
        }
    }

    private String getCurrentTimestamp() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
        return sdf.format(new Date());
    }

    public long insertOrder(String items, String note, double totalPrice, String userPhone, 
                            String deliveryType, String deliveryAddress, String deliveryPhone) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_ITEMS, items);
        values.put(COLUMN_NOTE, note);
        values.put(COLUMN_TOTAL_PRICE, totalPrice);
        values.put(COLUMN_USER_PHONE, userPhone);
        values.put(COLUMN_TIMESTAMP, getCurrentTimestamp());
        values.put(COLUMN_DELIVERY_TYPE, deliveryType);
        values.put(COLUMN_DELIVERY_ADDRESS, deliveryAddress);
        values.put(COLUMN_DELIVERY_PHONE, deliveryPhone);
        long newRowId = db.insert(TABLE_ORDERS, null, values);
        db.close();
        return newRowId;
    }

    public long insertOrder(String items, String note, double totalPrice, String userPhone) {
        return insertOrder(items, note, totalPrice, userPhone, "自提", null, null);
    }

    public long insertOrder(String items, String note, double totalPrice) {
        return insertOrder(items, note, totalPrice, null, "自提", null, null);
    }

    public List<Order> getAllOrders() {
        List<Order> orders = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            Cursor cursor = db.query(TABLE_ORDERS, null, null, null, null, null, COLUMN_ID + " DESC");

            if (cursor != null && cursor.moveToFirst()) {
                do {
                    Order order = cursorToOrder(cursor);
                    orders.add(order);
                } while (cursor.moveToNext());
            }

            if (cursor != null) {
                cursor.close();
            }
        } catch (Exception e) {
            Log.e(TAG, "获取订单列表时出错: " + e.getMessage());
        } finally {
            db.close();
        }
        
        return orders;
    }

    public List<Order> getOrdersByUserPhone(String userPhone) {
        List<Order> orders = new ArrayList<>();
        if (userPhone == null || userPhone.isEmpty()) {
            return orders;
        }
        
        SQLiteDatabase db = this.getReadableDatabase();
        
        try {
            String selection = COLUMN_USER_PHONE + " = ?";
            String[] selectionArgs = {userPhone};
            
            Cursor cursor = db.query(TABLE_ORDERS, null, selection, selectionArgs, null, null, COLUMN_ID + " DESC");

            if (cursor != null && cursor.moveToFirst()) {
                do {
                    Order order = cursorToOrder(cursor);
                    orders.add(order);
                } while (cursor.moveToNext());
            }

            if (cursor != null) {
                cursor.close();
            }
        } catch (Exception e) {
            Log.e(TAG, "按手机号获取订单时出错: " + e.getMessage());
        } finally {
            db.close();
        }
        
        return orders;
    }
    
    // 从游标创建订单对象，避免代码重复
    private Order cursorToOrder(Cursor cursor) {
        int id = cursor.getInt(cursor.getColumnIndexOrThrow(COLUMN_ID));
        String items = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_ITEMS));
        String note = cursor.getString(cursor.getColumnIndexOrThrow(COLUMN_NOTE));
        double totalPrice = cursor.getDouble(cursor.getColumnIndexOrThrow(COLUMN_TOTAL_PRICE));
        
        Order order = new Order(id, items, note, totalPrice);
        
        // 获取用户手机号
        int userPhoneIndex = cursor.getColumnIndex(COLUMN_USER_PHONE);
        if (userPhoneIndex != -1) {
            String userPhone = cursor.getString(userPhoneIndex);
            order.setUserPhone(userPhone);
        }
        
        // 获取时间戳
        int timestampIndex = cursor.getColumnIndex(COLUMN_TIMESTAMP);
        if (timestampIndex != -1) {
            String timestamp = cursor.getString(timestampIndex);
            order.setTimestamp(timestamp);
        }
        
        // 获取送餐方式
        int deliveryTypeIndex = cursor.getColumnIndex(COLUMN_DELIVERY_TYPE);
        if (deliveryTypeIndex != -1) {
            String deliveryType = cursor.getString(deliveryTypeIndex);
            order.setDeliveryType(deliveryType);
        }
        
        // 获取送餐地址
        int deliveryAddressIndex = cursor.getColumnIndex(COLUMN_DELIVERY_ADDRESS);
        if (deliveryAddressIndex != -1) {
            String deliveryAddress = cursor.getString(deliveryAddressIndex);
            order.setDeliveryAddress(deliveryAddress);
        }
        
        // 获取送餐电话
        int deliveryPhoneIndex = cursor.getColumnIndex(COLUMN_DELIVERY_PHONE);
        if (deliveryPhoneIndex != -1) {
            String deliveryPhone = cursor.getString(deliveryPhoneIndex);
            order.setDeliveryPhone(deliveryPhone);
        }
        
        return order;
    }
    
    // 删除订单
    public boolean deleteOrder(int orderId) {
        SQLiteDatabase db = this.getWritableDatabase();
        int result = db.delete(TABLE_ORDERS, COLUMN_ID + " = ?", new String[] { String.valueOf(orderId) });
        db.close();
        return result > 0;
    }
}